import os
import config.database
import sql

try:
    import flask
except ImportError:
    os.system('pip install flask')
    import flask

try:
    import json
except ImportError:
    os.system('pip install json')
    import json
try:
    import pymysql
except ImportError:
    os.system('pip install pymysql')
    import pymysql

from flask import Blueprint, jsonify, render_template

sqlilabs = Blueprint('sqlilabs', __name__)
try:
    db = pymysql.connect(host=config.database.HOST,
                         user=config.database.USERNAME,
                         password=config.database.PASSWORD,
                         db=config.database.DATABASE,
                         charset='UTF8')
except:
    sql.found_table()
    db = pymysql.connect(host=config.database.HOST,
                         user=config.database.USERNAME,
                         password=config.database.PASSWORD,
                         db=config.database.DATABASE,
                         charset='UTF8')


# @sqlilabs.route('/level1')
# def level1():
#     try:
#         # cursor = db.cursor()
#         # sql = "select * from user where id = " + userid + ";"
#         # cursor.execute(sql)
#         # results = cursor.fetchall()
#         return render_template('tables.html')
#     except Exception as ex:
#         print(ex)
#         return 'error'


@sqlilabs.route('/level1/<userid>')
def level1(userid):
    try:
        cursor = db.cursor()
        sql = "select * from user where id = " + userid + ";"
        cursor.execute(sql)
        results = cursor.fetchall()
        return render_template('tables.html', list=results)
    except Exception as ex:
        print(ex)
        return 'error'


@sqlilabs.route('/level2/<userid>')
def level2(userid):
    try:
        cursor = db.cursor()
        sql = "select * from user where id = '" + userid + "';"
        cursor.execute(sql)
        results = cursor.fetchall()
        # print(jsonify(results))
        # print(json.dumps(results, ensure_ascii=False))
        return render_template('tables.html', list=results)
    except Exception as ex:
        print(ex)
        return 'error'
